SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE FUNCTION SSB.StateStreetIdsNotInARTEMIS(@ExportId INT, @SSBImportId INT)
RETURNS TABLE
AS RETURN
(
	
	SELECT  ssr.StateStreetIdentifier
		  --, ai.AssetId
		  --, ai.*
	FROM    FSFundModel.dbo.StateStreetRecon AS ssr
			--INNER JOIN FSFundModel.Reporting.AssetIdentifier AS ai ON ai.AssetId = ssr.StateStreetIdentifier
	WHERE   ssr.ImportId = @SSBImportId
			--AND ai.ExportId = @ExportId
	EXCEPT 

	SELECT  ai.AssetId

	FROM    FSFundModel.Reporting.AssetIdentifier AS ai
	INNER JOIN  FSFundModel.Reporting.Asset AS a ON a.InstrumentId = ai.InstrumentId AND a.ExportId = ai.ExportId
	AND a.LotId = ai.LotId AND a.PortfolioId = ai.PortfolioId
	WHERE   ai.ExportId = @ExportId
	AND a.IsActiveEndOfDay =1


)
GO
EXEC sp_addextendedproperty N'VirtualFolder', N'SSB', 'SCHEMA', N'SSB', 'FUNCTION', N'StateStreetIdsNotInARTEMIS', NULL, NULL
GO
